<?php

class Khoa extends Model
{
    public function selectAll()
    {
        $sql = <<<SQL
SELECT k.dv_id AS k_id, dv_ten AS k_ten
FROM khoa k NATURAL JOIN donvi
SQL;

        return $this->_db->run($sql);
    }

    public function getTenKhoa($id)
    {
        $sql = <<<SQL
SELECT dv_ten FROM donvi NATURAL JOIN khoa WHERE donvi.dv_id = :id
SQL;
        $data = $this->_db->run($sql, array('id' => $id));
        if ($data) {
            return $data[0]['dv_ten'];
        } else {
            return null;
        }
    }

    public function update_ten($k_id, $k_ten)
    {
        $sql = <<<SQL
UPDATE donvi SET dv_ten = :ten WHERE dv_id = :id
SQL;
        $this->_db->run($sql, array('ten' => $k_ten, 'id' => $k_id));
    }

    public function addKhoa($ten)
    {
        $sql = <<<SQL
INSERT INTO donvi(dv_ten) VALUES(:ten)
SQL;
        $this->_db->run($sql, array('ten' => $ten));

        $newId = $this->_db->lastInsertId();
        $this->insert(array('dv_id' => $newId));

        return $newId;
    }

    public function delKhoa($id)
    {
        # xóa các cán bộ giảng dạy các bộ môn trong khoa
        $sql = <<<SQL
DELETE FROM cbgiangday
WHERE bm_id IN (SELECT bm_id FROM bomon WHERE dv_id = :id)
SQL;
        $this->_db->run($sql, array('id' => $id));

        # Xóa các bộ môn thuộc khoa
        $this->_db->run('DELETE FROM bomon WHERE dv_id = :id', array('id' => $id));

        # Xóa khoa
        $this->_db->run('DELETE FROM khoa WHERE dv_id = :id', array('id' => $id));

    }
}
